DCF Analysis (Monthly Periods) (Excel XLSX)

People hate math, and you have to do a little of it to convert your discount rates to monthly periods properly.

This is a general use monthly DCF (discounted cash flow) Analysis that lets the user enter in cash flow for up to 120 periods, including a period 0. There is also an annual summary (annual periods will display the NPV (net present value) of the monthly periods as that is ultimately more accurate).

If you did your own calculation of NPV on annual periods only, the value would be different by nature. The point of such an analysis is to figure out the present value of future cash flows as well as measure that present value against any initial investments.

Based on the discount rate defined, the NPV could be greater than or less than 0. This is often used for valuation in all sorts of uses. The model solves for monthly and annual IRR (which is the discount rate that results in a NPV of 0).

Other general financial metrics are also calculated based on the data entry and include ROI, minimum cash balance, equity multiple (based on all negative cash flows against positive cash flows), total and net cash returned.

The number of periods taken into account can be adjusted from 0 to 120 in order to allow dynamic analysis if for whatever reason you want to only see a certain number of periods worth of information being calculated.

Finally, there are a few visualizations that make it easier to see the effect of the time value of money. This means a cumulative value chart for the DCF and non-discounted CF.

The template is for general use so any set of cash flows that you want to use with it would be fine as long as it is not more than 120 periods. Just plug in the info, discount rate, and the model will calculate everything from there.